﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using Microsoft.Win32;
using System.Globalization;
using System.Reflection;
using System.ComponentModel;

namespace ReklamaWPF.Grids
{
    /// <summary>
    /// Interaction logic for Pivot1Grid.xaml
    /// </summary>
    public partial class Pivot1Grid : UserControl, INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;
        protected void OnPropertyChanged(string name)
        {
            PropertyChangedEventHandler handler = PropertyChanged;
            if (handler != null)
            {
                handler(this, new PropertyChangedEventArgs(name));
            }
        }

        private string _selDate;
        public string SelDate
        {
            get { return _selDate; }
            set
            {
                if (value != _selDate)
                {
                    _selDate = value;
                    OnPropertyChanged("SelDate");
                }
            }
        }

        public Pivot1Grid()
        {
            InitializeComponent();
            //SelDate = DateTime.Now.ToShortDateString();
            datePicker1.SelectedDate = DateTime.Now;
            
        }

        private void datePicker1_SelectedDateChanged(object sender, SelectionChangedEventArgs e)
        {
            //SelDate = datePicker1.SelectedDate.Value.ToShortDateString();
            DataBase db = new DataBase("192.168.100.100\\SQLEXPRESS", "sa", "tornado", "PASystem");
            DataTable tb = db.getTable("select Date,Period_H,Duration,free,Rejected from AdvertisingData left join Clips on Clip_Id=Clips.id join Blocks on [Time]>[Time_from] and [Time]<[Time_to] where dateadd(mm, datediff(mm, 0, '" + datePicker1.Text + "'), 0)<=Date and dateadd(ms,-3,dateadd(mm,datediff(m,0,'" + datePicker1.Text + "')+1,0))>=Date");
            pivotGridControl1.DataSource = tb;
           
        }

        public void ExportData()
        {
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.Filter = "Excel Files|*.xls";
            if (saveDialog.ShowDialog().Value)
            {
                string file = saveDialog.FileName;
                ExportExcel(file);
            }
        }

        private void ExportExcel(string filename)
        {
            CultureInfo myCIintl = new CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
            Microsoft.Office.Interop.Excel._Workbook oWBold = null;
            Microsoft.Office.Interop.Excel._Worksheet oSheetold = null;

            Microsoft.Office.Interop.Excel.Application oXLold = new Microsoft.Office.Interop.Excel.Application();
            oXLold.Visible = false;


            oWBold = (Microsoft.Office.Interop.Excel._Workbook)(oXLold.Workbooks.Add(Missing.Value)); //.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
            oSheetold = (Microsoft.Office.Interop.Excel._Worksheet)oWBold.ActiveSheet;

            string cell_info = "";
            TimeSpan cellTime;
            string cellTS = "";
            for(int i=0;i<pivotGridControl1.ColumnCount;i++){

               // if (pivotGridControl1.GetFieldByArea(DevExpress.Xpf.PivotGrid.FieldArea.ColumnArea, i) != null)
               // {
                    //pivotGridControl1.GetFieldValue(
                 DevExpress.Xpf.PivotGrid.PivotGridField pivotField=pivotGridControl1.GetFieldByArea(DevExpress.Xpf.PivotGrid.FieldArea.ColumnArea, 0);
                if (pivotGridControl1.GetFieldValue(pivotField, i)!=null){
                   // oSheetold.get_Range((char)(66 + i) + (1).ToString(), (char)(66 + i) + (1).ToString()).Cells.NumberFormat = "Text";
                    string val = pivotGridControl1.GetFieldValue(pivotField, i).ToString();
                    oSheetold.get_Range((char)(66 + i) + (1).ToString(), (char)(66 + i) + (1).ToString()).Value2 = val;
                    oSheetold.get_Range((char)(66 + i) + (1).ToString(), (char)(66 + i) + (1).ToString()).Font.Bold=true;  
                }

                for (int j = 0; j < pivotGridControl1.RowCount; j++)
                {
                    pivotField = pivotGridControl1.GetFieldByArea(DevExpress.Xpf.PivotGrid.FieldArea.RowArea, 0);
                    if (pivotGridControl1.GetFieldValue(pivotField, j) != null)
                    {
                       // oSheetold.get_Range("A" + (j + 2).ToString(), "A" + (j + 2).ToString()).Cells.NumberFormat = "Text";
                        string val = DateTime.Parse(pivotGridControl1.GetFieldValue(pivotField, j).ToString()).ToShortDateString();
                        oSheetold.get_Range("A" + (j + 2).ToString(), "A" + (j + 2).ToString()).Value2 = val;
                        oSheetold.get_Range("A" + (j + 2).ToString(), "A" + (j + 2).ToString()).Font.Bold=true;
                    }
                    if (pivotGridControl1.GetCellValue(i, j) != null)
                    {
                        cell_info = pivotGridControl1.GetCellValue(i, j).ToString();
                        cellTime = new TimeSpan(long.Parse(cell_info));
                    }
                    else { cellTime = new TimeSpan(0); }
                    oSheetold.get_Range((char)(66 + i) + (j + 2).ToString(), (char)(66 + i) + (j + 2).ToString()).Value = cellTime.ToString();
                }
            }


            oWBold.SaveAs(filename);
            oXLold.Quit();
            oSheetold = null;
            oWBold = null;
            oXLold = null;

            myCIintl = new CultureInfo("lt-LT");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;

        }
    }
}
